unit uMain;

interface

uses
  ZConnection, ZDataset;
  
type
  TMainExecute = class
  private
    FConnection: TZConnection;
    FQuery: TZQuery;

    FMdbsDir: string;
    FOutDir: string;
    FDBHost: string;
    FDBPort: Integer;
    FDBUser: string;
    FDBPwd: string;
    FDatabase: string;
    FDBNoIndex: Boolean;
    procedure HandleCmdLine;
    function ExecuteQuery(const strSql: string): Integer;
    function ExecuteScript(const strSql: string): Boolean;    
  public
    constructor Create;
    destructor Destroy; override;
    procedure Execute;    
  end;

var
  AppPath: string;
  G_MainExecute: TMainExecute;
  
implementation

uses
  SysUtils,
  Windows,
  Classes,
  ActiveX,
  IniFiles,
  DB,
  ADODB,
  ComObj;
  
procedure DebugOut(const str: string);
begin
  WriteLn(str);
end;

{ TMainExecute }

constructor TMainExecute.Create;
begin
  FDBHost := '';
  FDBPort := 3306;
  FDBUser := '';
  FDBPwd := '';
  FDatabase := '';
  FMdbsDir := '';
  FOutDir := 'Result';
  FDBNoIndex := False;
  FConnection := TZConnection.Create(nil);
  FQuery := TZQuery.Create(nil);
  FQuery.Connection := FConnection;
  AppPath := ExtractFilePath(ParamStr(0));
  HandleCmdLine;
  inherited;
end;

destructor TMainExecute.Destroy;
begin
  FQuery.Free;
  FConnection.Free;
  inherited;
end;

procedure TMainExecute.HandleCmdLine;
var
  i: Integer;
  str: string;
begin
  i := 1;
  while i <= ParamCount do
  begin
    if SameText('-h', ParamStr(i)) or
       SameText('-host', ParamStr(i)) then
    begin
      Inc(i);
      str := Trim(ParamStr(i));
      if Pos(':', str)>0 then
      begin
        FDBHost := Copy(str, 1, Pos(':', str)-1);
        FDBPort := StrToIntDef(
                    Copy(str, Pos(':', str)+1, Length(str)-Pos(':', str)), FDBPort)
      end else
        FDBHost := str;
    end else if SameText('-u', ParamStr(i)) or
       SameText('-user', ParamStr(i)) then
    begin
      Inc(i);
      FDBUser := ParamStr(i);
    end else if SameText('-p', ParamStr(i)) or
       SameText('-pwd', ParamStr(i)) then
    begin
      Inc(i);
      FDBPwd := ParamStr(i);
    end else if SameText('-d', ParamStr(i)) or
       SameText('-dbname', ParamStr(i)) then
    begin
      Inc(i);
      FDatabase := ParamStr(i);
    end else if SameText('-m', ParamStr(i)) or
       SameText('-mdbdir', ParamStr(i)) then
    begin
      Inc(i);
      FMdbsDir := ParamStr(i);
    end else if SameText('-o', ParamStr(i)) or
       SameText('-outdir', ParamStr(i)) then
    begin
      Inc(i);
      FOutDir := ParamStr(i);
    end else if SameText('-n', ParamStr(i)) or
       SameText('-noindex', ParamStr(i)) then
    begin
      FDBNoIndex := True;
      Inc(i);
    end else
      Inc(i);
  end;
  if FDBHost<>'' then
  begin
    FConnection.Protocol := 'mysql';
    FConnection.HostName := FDBHost;
    FConnection.Port := FDBPort;
    FConnection.User := FDBUser;
    FConnection.Password := FDBPwd;
    FConnection.Database := FDatabase;
  end;
end;

function TMainExecute.ExecuteQuery(const strSql: string): Integer;
begin
  Result := -1;
  try
    FQuery.SQL.Text := strSql;
    FQuery.Open;
    result := FQuery.RecordCount;
  except
  on E:Exception do
    begin
      DebugOut(Format('[error]ExecuteQuery sql=%s error=%s', [strSql, E.Message]));
    end;
  end;
end;

function TMainExecute.ExecuteScript(const strSql: string): Boolean;
begin
  result := False;
  try
    FQuery.SQL.Text := strSql;
    FQuery.ExecSQL;
    Result := True;
  except
  on E: Exception do
    begin
      DebugOut(Format('[error]ExecuteScript sql=%s error=%s', [strSql, E.Message]));
    end;
  end;
end;

procedure TMainExecute.Execute;
var
  strLine, strMdb: string;
  i, ii, lastIdx, iRet, iFoundCount: Integer;
  tables: TStringList;
  mdbFiles: TStringList;
  findedMobiles: TStringHash;
  ADOQuery: TADOQuery;
  mobile: Integer;
  fsCsv: TFileStream;
  procedure SelectFiles_r(const dir: string);
  var
    searchRec: TSearchRec;
  begin
    if FindFirst(dir + '\*.*', faAnyFile, SearchRec)=0 then
    begin
       repeat
         if (searchRec.Attr=faDirectory) or (searchrec.Attr=fmShareDenyRead) then
         begin
           if (searchRec.Name<>'.') and (searchRec.Name<>'..') then
             continue //SelectFiles_r(dir + '\' + searchRec.Name);
         end else if SameText(ExtractFileExt(searchRec.Name), '.mdb') then
            mdbFiles.Add(dir + '\' + searchRec.Name);
       until FindNext(searchRec)<>0;
       SysUtils.FindClose(searchRec);
    end;
  end;
const
  CONN_STR = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False';
begin
  tables := TStringList.Create;
  mdbFiles := TStringList.Create;
  findedMobiles := TStringHash.Create(10000);
  ADOQuery := TADOQuery.Create(nil);
  try
    try
      if (Pos(':',  FOutDir)>0) or
         (Pos('\\', FOutDir)>0) or
         (Pos('//', FOutDir)>0) then
      else
        FOutDir := AppPath + FOutDir;
      if (Pos(':',  FMdbsDir)>0) or
         (Pos('\\', FMdbsDir)>0) or
         (Pos('//', FMdbsDir)>0) then
      else
        FMdbsDir := AppPath + FMdbsDir;
      FOutDir := IncludeTrailingPathDelimiter(FOutDir);
      FMdbsDir := IncludeTrailingPathDelimiter(FMdbsDir);
      ForceDirectories(FOutDir);
      ForceDirectories(FMdbsDir);
      FConnection.Connect;
      FConnection.GetTableNames('', tables);
      if not FDBNoIndex then
      for i := 0 to tables.Count - 1 do
      begin
        DebugOut(Format('add index [%s]', [tables[i]]));
        if not ExecuteScript(Format('ALTER TABLE %s add INDEX enmobile_index (enmobile);', [tables[i]])) then
         ;// Exit;
      end;

      SelectFiles_r(FMdbsDir);
      DebugOut(Format('load %d .mdb files', [mdbFiles.Count]));
      for i := 0 to mdbFiles.Count - 1 do
      begin
        if FileExists(FOutDir + ChangeFileExt(ExtractFileName(mdbFiles[i]), '.csv')) then
          fsCsv := TFileStream.Create(FOutDir + ChangeFileExt(ExtractFileName(mdbFiles[i]), '.csv'), fmOpenWrite)
        else
          fsCsv := TFileStream.Create(FOutDir + ChangeFileExt(ExtractFileName(mdbFiles[i]), '.csv'), fmCreate);
        fsCsv.Position := fsCsv.Size;
        try
          strMdb := ExtractFileName(mdbFiles[i]);
          strLine := 'mdbfile,tablename,mobile,enmobile,mdate,mip,reginfo,fromurl'#13#10;
          fsCsv.Write(PChar(strLine)^, Length(strLine));
          if ADOQuery.Active then
            ADOQuery.Close;
          ADOQuery.ConnectionString := Format(CONN_STR, [mdbFiles[i]]);
          ADOQuery.SQL.Text := 'SELECT mobile,mdate,mip,reginfo,fromurl FROM mobile;';
          ADOQuery.Open;
          iFoundCount := 0;
          DebugOut(Format('processing mdb=%s recordcount=%d please waiting.....', [strMdb, ADOQuery.RecordCount]));
          while not ADOQuery.Eof do
          with ADOQuery do
          begin
            if findedMobiles.ValueOf(Fields[0].AsString)<>1 then
            begin
              for ii := 0 to tables.Count - 1 do
              begin
                iRet := ExecuteQuery(Format('SELECT mobile FROM %s WHERE enmobile=''%s'';',
                                            [tables[ii], Fields[0].AsString]));
                if iRet=0 then
                  continue
                else if iRet=-1 then
                  Exit
                else if iRet>0 then
                begin
                  Inc(iFoundCount);
                  strLine := Format('%s,%s,%s,%s,%s,%s,%s,%s'#13#10,
                              [
                              strMdb, tables[ii], FQuery.Fields[0].AsString,
                              Fields[0].AsString, Fields[1].AsString, Fields[2].AsString,
                              Fields[3].AsString, Fields[4].AsString
                              ]);
                  fsCsv.Write(PChar(strLine)^, Length(strLine));
                  findedMobiles.Add(Fields[0].AsString, 1);
                  break;
                end;
              end;
            end;
            DebugOut(Format('[%s] found %d record', [strMdb, iFoundCount]));
            Next;
          end;
        finally
          fsCsv.Free;
        end;
      end;
    except
    on E: Exception do
      begin
        DebugOut(E.Message);
      end;
    end;
  finally
    findedMobiles.Free;
    ADOQuery.Free;
    mdbFiles.Free;
    tables.Free;
  end;
end;

initialization
  CoInitialize(nil);

finalization
  CoUnInitialize;
  
end.
